-- 自行车外贸站数据库初始化脚本

-- 创建应用专用数据库用户（如果不存在）
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'bike_app_user') THEN
        CREATE USER bike_app_user WITH PASSWORD 'bike_app_password';
        RAISE NOTICE '应用用户 bike_app_user 创建成功';
    ELSE
        RAISE NOTICE '应用用户 bike_app_user 已存在';
    END IF;
END
$$;

-- 授予应用用户基本权限
GRANT CONNECT ON DATABASE bike_wholesale TO bike_app_user;
GRANT USAGE ON SCHEMA public TO bike_app_user;

-- 创建扩展（添加延迟确保扩展完全安装）
CREATE EXTENSION IF NOT EXISTS "uuid_ossp";

-- 等待扩展完全安装（添加短暂延迟）
SELECT pg_sleep(1);

-- 创建产品分类表（先创建不含外键约束的版本）
CREATE TABLE IF NOT EXISTS categories (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) UNIQUE NOT NULL,
    description TEXT,
    parent_id UUID,
    image_url VARCHAR(255),
    sort_order INTEGER DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建自行车产品表
CREATE TABLE IF NOT EXISTS bike_products (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    sku VARCHAR(50) UNIQUE NOT NULL,
    name VARCHAR(200) NOT NULL,
    slug VARCHAR(200) UNIQUE NOT NULL,
    description TEXT,
    short_description TEXT,
    category_id UUID REFERENCES categories(id),
    brand VARCHAR(100),
    model VARCHAR(100),
    frame_size VARCHAR(20),
    wheel_size VARCHAR(20),
    material VARCHAR(50),
    color VARCHAR(50),
    gears INTEGER,
    brake_type VARCHAR(50),
    weight DECIMAL(5,2),
    price DECIMAL(10,2) NOT NULL,
    sale_price DECIMAL(10,2),
    cost_price DECIMAL(10,2),
    stock_quantity INTEGER DEFAULT 0,
    min_stock_level INTEGER DEFAULT 10,
    is_featured BOOLEAN DEFAULT FALSE,
    is_active BOOLEAN DEFAULT TRUE,
    images JSONB DEFAULT '[]',
    specifications JSONB DEFAULT '{}',
    tags TEXT[],
    view_count INTEGER DEFAULT 0,
    rating DECIMAL(3,2) DEFAULT 0,
    review_count INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);



-- 添加自引用外键约束（在表创建完成后）
ALTER TABLE categories ADD CONSTRAINT fk_categories_parent 
FOREIGN KEY (parent_id) REFERENCES categories(id) DEFERRABLE;

-- 创建索引以提高查询性能
CREATE INDEX IF NOT EXISTS idx_bike_products_category ON bike_products(category_id);
CREATE INDEX IF NOT EXISTS idx_bike_products_price ON bike_products(price);
CREATE INDEX IF NOT EXISTS idx_bike_products_featured ON bike_products(is_featured) WHERE is_featured = true;

-- 插入示例数据（使用UUID函数生成ID）
INSERT INTO categories (id, name, slug, description, sort_order) VALUES 
(uuid_generate_v4(), '山地自行车', 'mountain-bikes', '专业山地自行车系列', 1),
(uuid_generate_v4(), '公路自行车', 'road-bikes', '高速公路自行车系列', 2),
(uuid_generate_v4(), '城市自行车', 'city-bikes', '城市通勤自行车系列', 3)
ON CONFLICT (slug) DO NOTHING;

-- 授予应用用户对表的操作权限
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO bike_app_user;

-- 授予序列权限（用于自增ID）
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO bike_app_user;

-- 授予扩展函数使用权限
GRANT EXECUTE ON FUNCTION uuid_generate_v4() TO bike_app_user;

-- 创建更新时间的触发器
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_bike_products_updated_at BEFORE UPDATE ON bike_products FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();